package edu.unsw.comp9321.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.unsw.comp9321.common.ServiceLocatorException;

public class BookingDAOImpl implements BookingDAO {

	private Connection connection;

	public BookingDAOImpl() throws ServiceLocatorException, SQLException {
		connection = DBConnectionFactory.getConnection();
	}

	@Override
	public List<BookingDTO> findAll() {
		List<BookingDTO> list = new ArrayList<BookingDTO>();
		try {
			Statement stmnt = connection.createStatement();
			String query = "SELECT * FROM booking";
			ResultSet res = stmnt.executeQuery(query);
			while (res.next()) {
				int id = res.getInt("booking_id");
				int carId = res.getInt("car_id");
				String customerName = res.getString("customer_name");
				int customerPin = res.getInt("customer_pin");
				Date bookingDate = res.getDate("booking_date");
				Date startDate = res.getDate("start_date");
				Date endDate = res.getDate("end_date");
				list.add(new BookingDTO(id, carId,customerName,customerPin, bookingDate, startDate, endDate));
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<BookingDTO> findBookingsWithCarIds(List<Integer> carIds) {
		List<BookingDTO> list = new ArrayList<BookingDTO>();
		try {
			String query = "SELECT * FROM booking " +
					"WHERE car_id = ?;";
			for (int carId: carIds) {
				PreparedStatement stmnt = connection.prepareStatement(query);
				stmnt.setInt(1, carId);
				ResultSet res = stmnt.executeQuery();
				while (res.next()) {
					int id = res.getInt("booking_id");
					int bookingCarId = res.getInt("car_id");
					String customerName = res.getString("customer_name");
					int customerPin = res.getInt("customer_pin");
					Date bookingDate = res.getDate("booking_date");
					Date startDate = res.getDate("start_date");
					Date endDate = res.getDate("end_date");
					list.add(new BookingDTO(id, bookingCarId,customerName,customerPin, bookingDate, startDate, endDate));
				}
				res.close();
				stmnt.close();
			}
		} catch (Exception e) {

		}
		return list;
	}

	@Override
	public BookingDTO findBookingWithId(int bookingId) {
		BookingDTO booking = null;
		try {
			String query = "SELECT * FROM booking WHERE booking_id = ?";
			PreparedStatement stmnt = connection.prepareStatement(query);
			stmnt.setString(1, String.valueOf(bookingId));
			ResultSet res = stmnt.executeQuery();
			while (res.next()) {
				int id = res.getInt("booking_id");
				int bookingCarId = res.getInt("car_id");
				String customerName = res.getString("customer_name");
				int customerPin = res.getInt("customer_pin");
				Date bookingDate = res.getDate("booking_date");
				Date startDate = res.getDate("start_date");
				Date endDate = res.getDate("end_date");
				booking = new BookingDTO(id, bookingCarId,customerName,customerPin, bookingDate, startDate, endDate);
			}
			res.close();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return booking;
	}

	@Override
	public BookingDTO insertBooking(int carId, String customerName,
			Date startDate, Date endDate) {
		BookingDTO booking = null;
		try {
			int pin = 10000 + (int)(Math.random() * ((99999 - 10000) + 1));
			//Date bookingDate = new Date();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			String bookingDateStr = dateFormat.format(new Date());
			String startDateStr = dateFormat.format(startDate);
			String endDateStr = dateFormat.format(endDate);
			Statement stmnt1 = connection.createStatement();
			String insertQuery = "INSERT INTO booking (car_id,customer_name,customer_pin,booking_date,start_date,end_date)"
					+ " VALUES ("+carId+",'"+customerName+"',"+pin+",'"+bookingDateStr+"','"+startDateStr+"','"+endDateStr+"');";
			stmnt1.executeUpdate(insertQuery);
			stmnt1.close();

			String selectQuery = "SELECT * FROM booking WHERE car_id = ? AND customer_name = ? AND booking_date = ? AND start_date = ? AND end_date = ? ;";
			PreparedStatement stmnt2 = connection.prepareStatement(selectQuery);
			stmnt2.setInt(1, carId);
			stmnt2.setString(2, customerName);
			stmnt2.setString(3, bookingDateStr);
			stmnt2.setString(4, startDateStr);
			stmnt2.setString(5, endDateStr);
			ResultSet res = stmnt2.executeQuery();
			while (res.next()) {
				int id = res.getInt("booking_id");
				int bookingCarIdSelect = res.getInt("car_id");
				String customerNameSelect = res.getString("customer_name");
				int customerPinSelect = res.getInt("customer_pin");
				Date bookingDateSelect = res.getDate("booking_date");
				Date startDateSelect = res.getDate("start_date");
				Date endDateSelect = res.getDate("end_date");
				booking = new BookingDTO(id, bookingCarIdSelect,customerNameSelect,customerPinSelect, bookingDateSelect, startDateSelect, endDate);
			}
			res.close();
			stmnt2.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
		}
		return booking;

	}

	@Override
	public boolean deleteBooking(int bookingId) {
		
		try {
			String query = "DELETE FROM booking WHERE booking_id = ?";
			PreparedStatement stmnt = connection.prepareStatement(query);
			stmnt.setString(1, String.valueOf(bookingId));
			stmnt.executeUpdate();
			stmnt.close();
		} catch (Exception e) {
			System.out.println("Caught Exception");
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
